{
    "metadata": {
        "kernelspec": {
            "name": "python3",
            "display_name": "Python 3 (ipykernel)",
            "language": "python"
        },
        "language_info": {
            "name": "python",
            "version": "3.8.10",
            "mimetype": "text/x-python",
            "codemirror_mode": {
                "name": "ipython",
                "version": 3
            },
            "pygments_lexer": "ipython3",
            "nbconvert_exporter": "python",
            "file_extension": ".py"
        }
    },
    "nbformat_minor": 2,
    "nbformat": 4,
    "cells": [
        {
            "cell_type": "markdown",
            "source": [
                "![Microsoft](https://raw.githubusercontent.com/microsoft/azuredatastudio/main/extensions/resource-deployment/images/microsoft-small-logo.png)\n",
                "## Deploy Azure SQL Edge container instance on a remote machine with Docker\n",
                "This notebook will use SSH to connect to the remote machine, use Docker to pull and run the Azure SQL Edge image and connect to it in Azure Data Studio\n",
                "\n",
                "\n",
                "<span style=\"color:red\"><font size=\"3\">Please press the \"Run all\" button to run the notebook</font></span>"
            ],
            "metadata": {
                "azdata_cell_guid": "565693e5-39d7-46cf-b0a1-3aa9945c3eed"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "### Install dependencies"
            ],
            "metadata": {
                "azdata_cell_guid": "b9e09157-54b4-4358-90a2-1a54419bd2cc"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "import sys,getpass,os,json,html,time\n",
                "\n",
                "def run_command():\n",
                "    print(\"Executing: \" + cmd)\n",
                "    !{cmd}\n",
                "    if _exit_code != 0:\n",
                "        sys.exit(f'Command execution failed with exit code: {str(_exit_code)}.\\n\\t{cmd}\\n')\n",
                "    print(f'Successfully executed: {cmd}')\n",
                "\n",
                "cmd = 'pip install paramiko'\n",
                "run_command()"
            ],
            "metadata": {
                "azdata_cell_guid": "a0a8cd9b-ab0f-4ba2-b568-0c68d950c8bf",
                "tags": [
                    "hide_input"
                ],
                "language": "python"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "### Setting variables"
            ],
            "metadata": {
                "azdata_cell_guid": "7c476b13-05f7-4975-bcb0-8c596bc01e6c"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "env_var_flag = \"AZDATA_NB_VAR_SA_PASSWORD\" in os.environ\n",
                "password_name = 'SQL Server sa account password'\n",
                "if env_var_flag:\n",
                "    container_name = os.environ[\"AZDATA_NB_VAR_DOCKER_CONTAINER_NAME\"]\n",
                "    sql_password = os.environ[\"AZDATA_NB_VAR_SA_PASSWORD\"]\n",
                "    sql_port = os.environ[\"AZDATA_NB_VAR_DOCKER_PORT\"]\n",
                "    docker_registry = os.environ[\"AZDATA_NB_VAR_DOCKER_REGISTRY\"]\n",
                "    docker_repository = os.environ[\"AZDATA_NB_VAR_DOCKER_REPOSITORY\"]\n",
                "    docker_imagetag = os.environ[\"AZDATA_NB_VAR_DOCKER_IMAGETAG\"]\n",
                "    docker_username = os.environ[\"AZDATA_NB_VAR_DOCKER_USERNAME\"]\n",
                "    docker_password = os.environ[\"AZDATA_NB_VAR_DOCKER_PASSWORD\"]\n",
                "    ssh_target = os.environ[\"AZDATA_NB_VAR_SSH_TARGET\"]\n",
                "    ssh_username = os.environ[\"AZDATA_NB_VAR_SSH_USERNAME\"]\n",
                "    ssh_password = os.environ[\"AZDATA_NB_VAR_SSH_PASSWORD\"]\n",
                "else:\n",
                "    container_name = 'SQLEDGE-' + time.strftime(\"%Y%m%d%H%M%S\", time.localtime())\n",
                "    docker_registry = 'mcr.microsoft.com'\n",
                "    docker_repository = 'azure-sql-edge-developer'\n",
                "    docker_imagetag = 'latest'\n",
                "    docker_username = ''\n",
                "    docker_password = ''\n",
                "    sql_password = getpass.getpass(prompt = password_name)\n",
                "    password_confirm = getpass.getpass(prompt = f'Confirm {password_name}')\n",
                "    if sql_password != password_confirm:\n",
                "        sys.exit(f'{password_name} does not match the confirmation password.')\n",
                "    sql_port = input('SQL Server port, default value is 1433')\n",
                "    if len(sql_port) == 0:\n",
                "        sql_port = '1433'\n",
                "    ssh_target = input('Remote machine name or address')\n",
                "    ssh_username = input('Username for remote machine')\n",
                "    ssh_password = input('Password for remote machine')\n",
                "\n",
                "print(f'{password_name}: ******')\n",
                "print(f'Remote machine: {ssh_target}')\n",
                "print(f'Remote machine username: {ssh_username}')\n",
                "print(f'Container name: {container_name}')\n",
                "print(f'Port: {sql_port}')\n",
                "print(f'Docker registry: {docker_registry}')\n",
                "print(f'Docker repository: {docker_repository}')\n",
                "print(f'Image tag: {docker_imagetag}')"
            ],
            "metadata": {
                "azdata_cell_guid": "c0af4e4e-4232-4a67-9a21-05a4d54fd0f4",
                "tags": [
                    "hide_input"
                ],
                "language": "python"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "### Log in to remote machine"
            ],
            "metadata": {
                "azdata_cell_guid": "28593625-0d7a-4dd0-b3bc-fdc3a5500d55"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "import paramiko\n",
                "client = paramiko.SSHClient()\n",
                "client.set_missing_host_key_policy(paramiko.AutoAddPolicy())\n",
                "client.connect(ssh_target,username=ssh_username, password=ssh_password)\n",
                "\n",
                "def run_command_remote():\n",
                "    stdin, stdout, stderr = client.exec_command(f'bash --login -c \"{cmd}\"')\n",
                "    for line in stdout:\n",
                "        print(line.strip('\\n'))\n",
                "    stderr = stderr.readlines()\n",
                "    if len(stderr) != 0:\n",
                "        for line in stderr:\n",
                "            print(line.strip('\\n'))\n",
                "        sys.exit(f'Command execution failed')\n",
                "    else:\n",
                "        print(f'Command executed successfully')"
            ],
            "metadata": {
                "azdata_cell_guid": "aadaa458-8c23-4b7a-a87e-cb2ee82046cf",
                "tags": [
                    "hide_input"
                ],
                "language": "python"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "### Check dependencies on remote machine"
            ],
            "metadata": {
                "azdata_cell_guid": "20d6ec5d-b706-4086-b583-6175c44740f2"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "cmd = 'docker version'\n",
                "run_command_remote()\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "f3f3946b-d950-477d-83d6-8bae320f619f",
                "tags": [
                    "hide_input"
                ],
                "language": "python"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "### List all the containers"
            ],
            "metadata": {
                "azdata_cell_guid": "85a12b48-3f7b-4181-bf91-90d3069bd0d8"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "cmd = 'docker ps -a'\n",
                "run_command_remote()"
            ],
            "metadata": {
                "azdata_cell_guid": "8b2874af-de7e-4a0a-9cad-3be397eaa3e9",
                "tags": [
                    "hide_input"
                ],
                "language": "python"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "### Pull the container image"
            ],
            "metadata": {
                "azdata_cell_guid": "92acb1bf-590d-426e-a9ee-2643c56dcdbe"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "if docker_username != '':\n",
                "    cmd = f'docker login {docker_registry} -u {docker_username} -p {docker_password}'\n",
                "    run_command_remote()\n",
                "cmd = f'docker pull {docker_registry}/{docker_repository}:{docker_imagetag}'\n",
                "run_command_remote()"
            ],
            "metadata": {
                "azdata_cell_guid": "58b2156b-da0c-47d5-9706-4bd9f630d28b",
                "tags": [
                    "hide_input"
                ],
                "language": "python"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "### Start a new container"
            ],
            "metadata": {
                "azdata_cell_guid": "442e71cd-551b-4b37-8b5a-b814efd27908"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "cmd = f'docker run -e ACCEPT_EULA=Y -e \"SA_PASSWORD={sql_password}\" -e \"MSSQL_PID=Developer\" -p {sql_port}:1433 --name {container_name} -d {docker_registry}/{docker_repository}:{docker_imagetag}'\n",
                "run_command_remote()"
            ],
            "metadata": {
                "azdata_cell_guid": "4cdcf011-06fd-4df4-bd20-3024d0a5ab9d",
                "tags": [
                    "hide_input"
                ],
                "language": "python"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "### List all the containers"
            ],
            "metadata": {
                "azdata_cell_guid": "072a9d13-4fb1-4114-9637-5255be9fcc81"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "cmd = f'docker ps -a'\n",
                "run_command_remote()"
            ],
            "metadata": {
                "azdata_cell_guid": "345dc24f-0028-47b4-b35b-aaac047b7a62",
                "tags": [
                    "hide_input"
                ],
                "language": "python"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "### Connect to Azure SQL Edge instance in Azure Data Studio\n",
                "It might take a couple minutes for the service to launch"
            ],
            "metadata": {
                "azdata_cell_guid": "c84bc075-f8b2-48a3-bfc7-f07a5b8be7a0"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "from IPython.display import *\n",
                "connectionParameter = '{\"serverName\":\"' + ssh_target + ',' + sql_port + '\",\"providerName\":\"MSSQL\",\"authenticationType\":\"SqlLogin\",\"userName\":\"sa\",\"password\":' + json.dumps(sql_password) + ',\"options\": {\"trustServerCertificate\":true}}'\n",
                "display(HTML('<br/><a href=\"command:azdata.connect?' + html.escape(connectionParameter)+'\"><font size=\"3\">Click here to connect to the Azure SQL Edge instance</font></a><br/>'))\n",
                "display(HTML('<br/><span style=\"color:red\"><font size=\"2\">NOTE: The Azure SQL Edge instance password is included in this link, you may want to clear the results of this code cell before saving the notebook.</font></span>'))"
            ],
            "metadata": {
                "azdata_cell_guid": "187a0067-a04c-4afb-a684-3103bb4522ae",
                "tags": [
                    "hide_input"
                ],
                "language": "python"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "### Stop and remove the container\n",
                "Please copy the output of this cell into the next empty code cell and run the cell to stop and remove the container"
            ],
            "metadata": {
                "azdata_cell_guid": "94322968-02f5-49e5-8ff1-47a9af3c5e83"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "remove_container_command = f'cmd = f\\'docker rm {container_name}\\'\\nrun_command_remote()'\n",
                "stop_container_command = f'cmd = f\\'docker stop {container_name}\\'\\nrun_command_remote()'\n",
                "print(stop_container_command)\n",
                "print(remove_container_command)"
            ],
            "metadata": {
                "azdata_cell_guid": "9b6c34c2-7a47-43f5-971d-ce9768fec587",
                "tags": [
                    "hide_input"
                ],
                "language": "python"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "code",
            "source": [
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "9f2c9d3a-b996-4977-81f5-05a79a2e0e12",
                "tags": [
                    "hide_input"
                ],
                "language": "python"
            },
            "outputs": [],
            "execution_count": null
        }
    ]
}